package top.lishuoboy.mybatis;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.map.MapUtil;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageRowBounds;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
import top.lishuoboy.dependency.base.db.MyDbUtil;
import top.lishuoboy.mybatis.bean.Class;
import top.lishuoboy.mybatis.bean.Student;
import top.lishuoboy.mybatis.bean.User;
import top.lishuoboy.mybatis.mapper.ClassMapper;
import top.lishuoboy.mybatis.mapper.SqlMapper;
import top.lishuoboy.mybatis.mapper.StudentMapper;
import top.lishuoboy.mybatis.mapper.UserMapper;
import top.lishuoboy.mybatis.vo.MyPage;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * @author lishuoboy
 */
@Slf4j
public class MyBatisTest {
    private static SqlSessionFactory sqlSessionFactory;

    // 创建 SqlSessionFactory
    static {
        try {
            log.info("创建 SqlSessionFactory start");
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

            // 设置 参数属性，优先级: 参数属性 > 外部 resource 配置文件属性 > xml内部属性
            Properties props = new Properties();
            props.setProperty("db.username", "root");

            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, props);

            // ds.getClass()==class com.alibaba.druid.pool.DruidDataSource
            log.debug("ds.getClass()=={}", sqlSessionFactory.getConfiguration().getEnvironment().getDataSource().getClass());
            log.info("创建 SqlSessionFactory end");
        } catch (IOException e) {
            log.error("创建 SqlSessionFactory 异常", e);
        }
    }

    public static void main(String[] args) {
//        resetDbData();
//        helloWorld();
//        annotation();
//        typeAlias();
//        mappers();
//        resultMap();
//        selectRepeatSql();
//        selectMap();
//        selectCache();
//        dynamicIf();
//        dynamicChoose();
//        dynamicWhereSetTrim();
//        dynamicForeachIn();
        sqlExecutor();
//        sqlSession();
//        selectLike();
//        batchInsert();
//        selectPage();
//        many2One();
//        one2Many();
//        insertGeneratedKeys();
//        _$();
//        discriminator();
//        bind();
//        pageHelperHelloWorld();
//        pageHelperUse();
    }

    /**
     * 创建 SqlSession
     */
    public static SqlSession getSqlSession() {
        log.info("创建 SqlSession");
        return sqlSessionFactory.openSession();
    }


    /**
     * 重置数据库表数据
     */
    public static void resetDbData() {
        DataSource dataSource = getSqlSession().getConfiguration().getEnvironment().getDataSource();
        MyDbUtil.resetData(dataSource, "db_sql/mybatis.sql");
    }

    /**
     * helloWorld
     */
    public static void helloWorld() {
        log.info("helloWorld start");
        SqlSession sqlSession = getSqlSession();
        User user;
//      方式一
        user = sqlSession.selectOne("top.lishuoboy.mybatis.mapper.UserMapper.selectById", 1);
        log.warn("user=={}", user);
//      方式二
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        user = userMapper.selectById(2);
        log.warn("user=={}", user);
        sqlSession.close();
        log.info("helloWorld end");
    }

    /**
     * 注解方式
     */
    public static void annotation() {
        log.info("annotation start");
        SqlSession sqlSession = getSqlSession();
        User user;
//      方式一 UserMapper可以没有selectById2方法，映射xml中有就行
        user = sqlSession.selectOne("top.lishuoboy.mybatis.mapper.UserMapper.selectById2", 1);
        log.warn("user=={}", user);
//      方式二
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        user = userMapper.selectById2(2);
        log.warn("user=={}", user);

        user = userMapper.selectById2_1(3);
        log.warn("user=={}", user);

        user = userMapper.selectById2_2(1, 2);
        log.warn("user=={}", user);

        user = userMapper.selectById2_3(2, 3);
        log.warn("user=={}", user);

        sqlSession.close();
        log.info("annotation end");
    }

    /**
     * 别名
     */
    public static void typeAlias() {
        log.info("typeAlias start");
        SqlSession sqlSession = getSqlSession();
        User user;
//      方式二
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        user = userMapper.selectById(1);
        log.warn("user=={}", user);
        user = userMapper.selectById3(1);
        log.warn("user=={}", user);
        sqlSession.close();
        log.info("typeAlias end");
    }

    /**
     * mappers
     */
    public static void mappers() {
        log.info("mappers start");
        SqlSession sqlSession = getSqlSession();

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper.selectList();
        log.warn("userList=={}", userList);

        // 查询表 并返回带主键的容器map<Integer,User>，见下面方法 sqlSession();

        // 返回对象唯一个，但是查到多个，报错：Expected one result (or null) to be returned by selectOne(), but found: 3
//        User user = userMapper.selectList2();
//        log.warn("user=={}", user);

        User user2 = new User().setId(666).setUserName("小六子");
        userMapper.insert(user2);

        userMapper.deleteById(1);

        User user1 = new User().setId(2).setUserName("李四四");
        userMapper.updateById(user1);

        sqlSession.commit();
        sqlSession.close();
        log.info("mappers end");
    }


    /**
     * resultMap
     * 驼峰字段映射三种方式
     */
    public static void resultMap() {
        log.info("resultMap start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList1 = userMapper.selectColumnAlias1();
        log.warn("userList1=={}", userList1);

        List<User> userList2 = userMapper.selectColumnAlias2();
        log.warn("userList2=={}", userList2);

        List<User> userList3 = userMapper.selectColumnAlias3();
        log.warn("userList3=={}", userList3);

        sqlSession.close();
        log.info("resultMap end");
    }

    /**
     * 重复sql片段复用
     */
    public static void selectRepeatSql() {
        log.info("selectRepeatSql start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper.selectRepeatSql();
        log.warn("userList=={}", userList);

        sqlSession.close();
        log.info("selectRepeatSql end");
    }

    /**
     * 万能 Map
     */
    public static void selectMap() {
        log.info("selectMap start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<Map> userList1 = userMapper.selectMap();
        log.warn("userList1=={}", userList1);
        List<LinkedHashMap> userList2 = userMapper.selectLinkedHashMap();
        log.warn("userList2=={}", userList2);

        sqlSession.close();
        log.info("selectMap end");
    }

    /**
     * 缓存，一级缓存、二级缓存，二级缓存是在 提交或关闭时存入。观察sql日志研究
     * 一级缓存始终开启，对同一个sqlSession有效
     * 二级缓存开启后，对不同sqlSession有效，
     * <p>
     * 映射语句文件中的所有 select 语句的结果将会被缓存。
     * 映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存。
     * 缓存会使用最近最少使用算法（LRU, Least Recently Used）算法来清除不需要的缓存。
     * 缓存不会定时进行刷新（也就是说，没有刷新间隔）。
     * 缓存会保存列表或对象（无论查询方法返回哪种）的 1024 个引用。
     * 缓存会被视为读/写缓存，这意味着获取到的对象并不是共享的，可以安全地被调用者修改，而不干扰其他调用者或线程所做的潜在修改。
     */
    public static void selectCache() {
        log.info("selectCache start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        SqlSession sqlSession2 = getSqlSession();
        UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);


        List<User> userList1 = userMapper.selectCache();
        log.warn("userList1=={}", userList1);
        List<User> userList2 = userMapper.selectCache();
        log.warn("userList2=={}", userList2);

        // 二级缓存是在 sqlSession 提交或关闭时存入
        sqlSession.commit();
//        sqlSession.close();
        List<User> userList3 = userMapper2.selectCache();
        log.warn("userList3=={}", userList3);
        // 对于另外一个select ，虽然查询语句一样，但是缓存无效
        List<User> userList4 = userMapper2.selectColumnAlias2();
        log.warn("userList4=={}", userList4);

        sqlSession2.close();
        log.info("selectCache end");
    }

    /**
     * 动态标签 if
     */
    public static void dynamicIf() {
        log.info("dynamicIf start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);


        List<User> userList1 = userMapper.dynamicIf(new User().setId(1).setUserName("张三"));
        log.warn("userList1=={}", userList1);

        List<User> userList2 = userMapper.dynamicIf(new User());
        log.warn("userList2=={}", userList2);

        sqlSession.close();
        log.info("dynamicIf end");
    }

    /**
     * 动态sql choose、when、otherwise
     */
    public static void dynamicChoose() {
        log.info("dynamicChoose start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList1 = userMapper.dynamicChoose(new User());
        log.warn("userList1=={}", userList1);

        List<User> userList2 = userMapper.dynamicChoose(new User().setId(1));
        log.warn("userList2=={}", userList2);

        List<User> userList3 = userMapper.dynamicChoose(new User().setUserName("张三"));
        log.warn("userList3=={}", userList3);

        List<User> userList4 = userMapper.dynamicChoose(new User().setId(1).setUserName("张三"));
        log.warn("userList4=={}", userList4);

        sqlSession.close();
        log.info("dynamicChoose end");
    }


    /**
     * 动态sql where、trim、set
     */
    public static void dynamicWhereSetTrim() {
        log.info("动态sql where、trim、set start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList1 = userMapper.dynamicWhere1(new User().setId(1));
        log.warn("userList1=={}", userList1);


        List<User> userList1_2 = null;
        try {
            userList1_2 = userMapper.dynamicWhere1(new User());
            log.warn("userList1_2=={}", userList1_2);
        } catch (Exception e) {
            log.error("没传参数会报错,看日志的sql语句，是错误的");
        }


        List<User> userList1_3 = null;
        try {
            userList1_3 = userMapper.dynamicWhere1(new User().setUserName("张三"));
            log.warn("userList1_3=={}", userList1_3);
        } catch (Exception e) {
            log.error("只传第二个传参数也会报错,看日志的sql语句，是错误的");
        }

        // dynamicWhere2 不会报错
        List<User> userList2 = userMapper.dynamicWhere2(new User());
        log.warn("userList2=={}", userList2);

        List<User> userList2_2 = userMapper.dynamicWhere2(new User().setUserName("张三"));
        log.warn("userList2_2=={}", userList2_2);

        // dynamicWhere3 不会报错
        List<User> userList3 = userMapper.dynamicWhere3(new User());
        log.warn("userList3=={}", userList3);

        List<User> userList3_2 = userMapper.dynamicWhere3(new User().setUserName("张三"));
        log.warn("userList3_2=={}", userList3_2);

        List<User> userList3_3 = userMapper.dynamicWhere3(new User().setId(1));
        log.warn("userList3_3=={}", userList3_3);


        userMapper.dynamicSetTrim(new User().setUserName("张三@@").setId(1));
        userMapper.dynamicSetTrim2(new User().setUserName("李四@@").setId(2));

        sqlSession.commit();
        sqlSession.close();
        log.info("动态sql where、trim、set end");
    }


    /**
     * 动态sql foreachIn 配合List Map
     */
    public static void dynamicForeachIn() {
        log.info("动态sql foreachIn start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List idList = ListUtil.list(false, 1, "2", 'a');
        log.warn("idList=={}", idList);
        List<User> userList = userMapper.dynamicForeachIn(idList);
        log.warn("userList=={}", userList);

        Object[] idArr = {1, "2", 'a'};
        log.warn("idArr=={}", idArr);
        List<User> userList2 = userMapper.dynamicForeachIn2(idArr);
        log.warn("userList2=={}", userList2);

        List idList3 = ListUtil.list(false, 1, 2);
        List nameList3 = ListUtil.list(false, "李四", "王五");
        Map map = MapUtil.builder("idList", idList3).put("nameList", nameList3).build();
        log.warn("map=={}", map);
        List<User> userList3 = userMapper.dynamicForeachIn3(map);
        log.warn("userList3=={}", userList3);

        sqlSession.commit();
        sqlSession.close();
        log.info("动态sql foreachIn end");
    }


    /**
     * 万能 sql 执行器
     */
    public static void sqlExecutor() {
        resetDbData();
        log.info("万能 sql 执行器 start");
        SqlSession sqlSession = getSqlSession();
        SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);

        Integer id;
        String name, pwd;

        List<User> userList = sqlMapper.selectList("select * from user", User.class);
        log.warn("userList=={}", userList);             // userList==[User(id=1, userName=张三, pwd=11111, pageNum=null, pageSize=null), User(id=2, userName=李四, pwd=12222, pageNum=null, pageSize=null), User(id=3, userName=王五, pwd=33331, pageNum=null, pageSize=null), User(id=4, userName=赵六, pwd=44444, pageNum=null, pageSize=null), User(id=5, userName=孙七, pwd=55555, pageNum=null, pageSize=null), User(id=6, userName=周八, pwd=66666, pageNum=null, pageSize=null)]

        List<Map<String, Object>> mapList1 = sqlMapper.selectList("select id,user_name from user");
        Map<String, Object> map = mapList1.get(0);
        id = (Integer) map.get("id");
        name = (String) map.get("user_name");
        log.warn("{}, {}", map.getClass(), map);        // class java.util.HashMap, {user_name=张三, id=1}
        log.warn("{}, {}", id.getClass(), id);          // class java.lang.Integer, 1
        log.warn("{}, {}", name.getClass(), name);      // class java.lang.String, 张三

        List<Map<String, String>> mapList2 = sqlMapper.selectList("select user_name, pwd from user");
        Map<String, String> map2 = mapList2.get(0);
        name = map2.get("user_name");
        pwd = map2.get("pwd");
        log.warn("{}, {}", map2.getClass(), map2);      // class java.util.HashMap, {user_name=张三, pwd=11111}
        log.warn("{}, {}", name.getClass(), name);      // class java.lang.String, 张三
        log.warn("{}, {}", pwd.getClass(), pwd);        // class java.lang.String, 11111

        MyPage myPage = new MyPage(2, 2, "pwd desc,id asc,salary").setSearchCount(true);
        List<Map<String, Object>> mapList3 = sqlMapper.selectList("select * from user", myPage);
        log.warn("mapList3=={}", mapList3);             // mapList3==Page{count=true, pageNum=2, pageSize=2, startRow=2, endRow=4, total=6, pages=3, reasonable=false, pageSizeZero=false}[{user_name=赵六, id=4, pwd=44444}, {user_name=王五, id=3, pwd=33331}]
        List<User> userList3 = sqlMapper.selectList("select * from user", myPage, User.class);
        log.warn("userList3=={}", userList3);           // userList3==[User(id=4, userName=赵六, pwd=44444, pageNum=null, pageSize=null), User(id=3, userName=王五, pwd=33331, pageNum=null, pageSize=null)]

        MyPage<Map<String, Object>> mapMyPage = sqlMapper.selectPage("select * from user", myPage);
        log.warn("mapMyPage=={}", mapMyPage);           // mapMyPage==MyPage(current=2, size=2, orders=pwd desc,id asc,salary, records=Page{count=true, pageNum=2, pageSize=2, startRow=2, endRow=4, total=6, pages=3, reasonable=false, pageSizeZero=false}[{user_name=赵六, id=4, pwd=44444}, {user_name=王五, id=3, pwd=33331}], searchCount=true, total=6, pages=3)
        MyPage<User> userMyPage = sqlMapper.selectPage("select * from user", myPage, User.class);
        log.warn("userMyPage=={}", userMyPage);         // userMyPage==MyPage(current=2, size=2, orders=pwd desc,id asc,salary, records=[User(id=4, userName=赵六, pwd=44444, pageNum=null, pageSize=null), User(id=3, userName=王五, pwd=33331, pageNum=null, pageSize=null)], searchCount=true, total=6, pages=3)

        myPage = new MyPage().setOrders("id desc,name");
        List<Map<String, Object>> mapList4 = sqlMapper.selectList("select * from class c,student s where c.id=s.class_id", myPage);
        log.warn("mapList4=={}", mapList4);             // mapList4==Page{count=true, pageNum=1, pageSize=10, startRow=0, endRow=10, total=3, pages=1, reasonable=false, pageSizeZero=false}[{class_id=2, name=二班, id=2}, {class_id=1, name=一班, id=1}, {class_id=1, name=一班, id=1}]
        MyPage<Map<String, Object>> mapMyPage2 = sqlMapper.selectPage("select * from class c,student s where c.id=s.class_id", myPage);
        log.warn("mapMyPage2=={}", mapMyPage2);         // mapMyPage2==MyPage(current=1, size=10, orders=c.id desc, c.name, searchCount=true, total=3, pages=1, records=Page{count=true, pageNum=1, pageSize=10, startRow=0, endRow=10, total=3, pages=1, reasonable=false, pageSizeZero=false}[{class_id=2, name=二班, id=2}, {class_id=1, name=一班, id=1}, {class_id=1, name=一班, id=1}])

        User user = sqlMapper.selectOne("select * from user limit 1", User.class);
        log.warn("user=={}", user);                     // user==User(id=1, userName=张三, pwd=11111, pageNum=null, pageSize=null)

        Map<String, Object> map3 = sqlMapper.selectOne("select id,user_name from user limit 1");
        id = (Integer) map3.get("id");
        name = (String) map3.get("user_name");
        log.warn("{}, {}", map3.getClass(), map3);      // class java.util.HashMap, {user_name=张三, id=1}
        log.warn("{}, {}", id.getClass(), id);          // class java.lang.Integer, 1
        log.warn("{}, {}", name.getClass(), name);      // class java.lang.String, 张三

        Map<String, String> map4 = sqlMapper.selectOne("select user_name,pwd from user limit 1");
        name = map4.get("user_name");
        pwd = map4.get("pwd");
        log.warn("{}, {}", map4.getClass(), map4);      // class java.util.HashMap, {user_name=张三, pwd=11111}
        log.warn("{}, {}", name.getClass(), name);      // class java.lang.String, 张三
        log.warn("{}, {}", pwd.getClass(), pwd);        // class java.lang.String, 11111


        List<Integer> intList = sqlMapper.selectOneColumn("select id from user");
        List<String> strList = sqlMapper.selectOneColumn("select pwd from user");
        List<Timestamp> dateList = sqlMapper.selectOneColumn("select update_time from user");
        List<BigDecimal> decimalList = sqlMapper.selectOneColumn("select salary from user");
        log.warn("{}, {}", intList.get(0).getClass(), intList.get(0));          // class java.lang.Integer, 1
        log.warn("{}, {}", strList.get(0).getClass(), strList.get(0));          // class java.lang.String, 11111
        log.warn("{}, {}", dateList.get(0).getClass(), dateList.get(0));        // class java.sql.Timestamp, 2021-11-11 11:11:11.0
        log.warn("{}, {}", decimalList.get(0).getClass(), decimalList.get(0));  // class java.math.BigDecimal, 11.123000

        id = sqlMapper.selectOneVal("select id,pwd from user limit 1");
        log.warn("{}, {}", id.getClass(), id);                          // class java.lang.Integer, 1

        name = sqlMapper.selectOneVal("select user_name from user limit 1");
        log.warn("{}, {}", name.getClass(), name);                      // class java.lang.String, 张三

        Long rowCount = sqlMapper.selectOneVal("select count(*) from user");
        log.warn("{}, {}", rowCount.getClass(), rowCount);              // class java.lang.Long, 6

        Integer count = sqlMapper.executeUpdate("update user set user_name = '张三三' where id <=3");
        log.warn("count=={}", count);           // count==3

        count = sqlMapper.executeUpdate("INSERT INTO user (id,user_name) VALUES (55, '五五'), (66, '六六')");
        log.warn("count=={}", count);           // count==2

        count = sqlMapper.executeUpdate("CREATE TABLE test ( user_name varchar(255) NULL)");
        log.warn("count=={}", count);           // count==0

        sqlSession.commit();
        sqlSession.close();
        log.info("万能 sql 执行器 end");
    }

    /**
     * sqlSession 更多方法
     */
    public static void sqlSession() {
        log.info("sqlSession 更多方法 start");
        SqlSession sqlSession = getSqlSession();

        User user = new User().setId(666).setUserName("小六子");
        int count = sqlSession.insert("top.lishuoboy.mybatis.mapper.UserMapper.insert", user);
        log.warn("count=={}", count);

        User user2 = sqlSession.selectOne("top.lishuoboy.mybatis.mapper.UserMapper.selectById4", 1);
        log.warn("user2=={}", user2);

        List<User> userList = sqlSession.selectList("top.lishuoboy.mybatis.mapper.UserMapper.selectById4", 1);
        log.warn("userList=={}", userList);

        Map<Integer, User> map = sqlSession.selectMap("top.lishuoboy.mybatis.mapper.UserMapper.selectById4", 1, "id");
        log.warn("map=={}", map);

        Map<Integer, User> map2 = sqlSession.selectMap("top.lishuoboy.mybatis.mapper.UserMapper.selectList4", "id");
        log.warn("map2=={}", map2);

        sqlSession.commit();
        sqlSession.close();
        log.info("sqlSession 更多方法 end");
    }


    /**
     * 模糊查询
     */
    public static void selectLike() {
        log.info("模糊查询 start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper.selectLike("%1%");
        log.warn("userList=={}", userList);
        List<User> userList2 = userMapper.selectLike2("%1");
        log.warn("userList2=={}", userList2);
        List<User> userList3 = userMapper.selectLike3("%1");
        log.warn("userList3=={}", userList3);
        List<User> userList4 = userMapper.selectLike4("%1");
        log.warn("userList4=={}", userList4);

        sqlSession.commit();
        sqlSession.close();
        log.info("模糊查询 end");
    }


    /**
     * 批量插入
     */
    public static void batchInsert() {
        log.info("批量插入 start");
        SqlSession sqlSession = getSqlSession();
        SqlSession sqlSessionB = sqlSessionFactory.openSession(ExecutorType.BATCH);
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        UserMapper userMapperB = sqlSessionB.getMapper(UserMapper.class);
        SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);
        TimeInterval timer = new TimeInterval();

        List<User> userList = ListUtil.list(false);
        int COUNT = 100000;
        timer.start();
        for (int i = 0; i < COUNT; i++) {
            // 将id设置为自增了，不setId，防止报错
            userList.add(new User().setUserName("张三" + i));
        }
        log.warn("创建userList用时=={}ms", timer.interval());

//      批量插入
        sqlMapper.executeUpdate("truncate user");
        timer.restart();
        timer.start();
        int count = userMapper.insertBatch(userList);
        log.warn("count=={}", count);
        sqlSession.commit();
        log.warn("insertBatch=={}ms", timer.interval());

//      单笔插入1(攒到一定数量的sql再给数据库执行，还是一条一条执行的，性能提高一倍，但是跟批量相比还差的远)
        sqlMapper.executeUpdate("truncate user");
        timer.restart();
        timer.start();
        for (int i = 0; i < COUNT; i++) {
            userMapperB.insertSingle(userList.get(i));
        }
        sqlSessionB.commit();
        log.warn("insertSingle1=={}ms", timer.interval());

//      单笔插入2
        sqlMapper.executeUpdate("truncate user");
        timer.restart();
        timer.start();
        for (int i = 0; i < COUNT; i++) {
            userMapper.insertSingle(userList.get(i));
        }
        sqlSession.commit();
        log.warn("insertSingle2用时=={}ms", timer.interval());

        sqlSession.commit();
        sqlSession.close();
        log.info("批量插入 end");
    }


    /**
     * 分页
     */
    public static void selectPage() {
        log.info("分页 start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        TimeInterval timer = new TimeInterval();
        timer.start();

//        分页方式一【可以】: limit
        timer.start();
        List<User> userList = userMapper.selectPage1(0, 2);
        log.warn("userList=={}", userList);

//        分页方式二【强烈不推荐】: rowBounds 。 淘汰 本质是从库里查全量，内存里分页，太耗资源
        RowBounds rowBounds = new RowBounds(0, 2);
        timer.restart();
        List<User> userList2 = sqlSession.selectList("top.lishuoboy.mybatis.mapper.UserMapper.selectPage2", "", rowBounds);
        log.warn("userList2=={}", userList2);

//        分页方式三【强烈推荐】: pageHelper，待补充


        sqlSession.commit();
        sqlSession.close();
        log.info("分页 end");
    }


    /**
     * 多对一
     */
    public static void many2One() {
        log.info("多对一 start");
        SqlSession sqlSession = getSqlSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        List<Student> studentList = studentMapper.select();
        log.warn("studentList=={}", studentList);

        // 关联查询——多对一，方式一：分步查询。先查询主表，再用主表的关联字段查关联表。执行了 1 + n 次 sql,性能低的很。可以通过 lazyLoadingEnabled和aggressiveLazyLoading 配置懒加载。
        List<Student> studentList2 = studentMapper.selectAssociation();
        log.warn("studentList2=={}", studentList2);

        // 关联查询——多对一，方式二【推荐】：连表查询association定义关联对象封装规则。执行了 1 次 sql,性能高。
        List<Student> studentList3 = studentMapper.selectAssociation2();
        log.warn("studentList3=={}", studentList3);

        // 关联查询——多对一，一对多，方式三【推荐】：级联属性封装结果。执行了 1 次 sql,性能高。
        List<Student> studentList4 = studentMapper.selectProperty();
        log.warn("studentList4=={}", studentList4);

        // 关联查询——多对一，一对多，方式四【推荐】：连表查询Map封装。执行了 1 次 sql,性能高。
        List<Map> mapList = studentMapper.selectMap();
        log.warn("mapList=={}", mapList);


        sqlSession.close();
        log.info("多对一 end");
    }

    /**
     * 一对多
     */
    public static void one2Many() {
        log.info("一对多 start");
        SqlSession sqlSession = getSqlSession();
        ClassMapper classMapper = sqlSession.getMapper(ClassMapper.class);

        List<Class> classList = classMapper.select();
        log.warn("classList=={}", classList);

        // 关联查询——多对一，方式一【不推荐】：分步查询。先查询主表，再用主表的关联字段查关联表。执行了 1 + n 次 sql,性能低的很。可以通过 lazyLoadingEnabled和aggressiveLazyLoading 配置懒加载。
        List<Class> classList2 = classMapper.selectCollection();
        log.warn("classList2=={}", classList2);

        // 关联查询——一对多，方式二【推荐】，collection定义关联集合封装规则，不使用columnPrefix。连表查询association定义关联对象封装规则。执行了 1 次 sql,性能高
        List<Class> classList3_1 = classMapper.selectCollection2_1();
        log.warn("classList3_1=={}", classList3_1);

        // 关联查询——一对多，方式二【推荐】，collection定义关联集合封装规则，使用columnPrefix。连表查询association定义关联对象封装规则。执行了 1 次 sql,性能高
        List<Class> classList3_2 = classMapper.selectCollection2_2();
        log.warn("classList3_2=={}", classList3_2);

        sqlSession.close();
        log.info("一对多 end");
    }

    /**
     * 插入/批量插入返回自增主键
     */
    public static void insertGeneratedKeys() {
        log.info("插入/批量插入返回自增主键 start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = new User().setUserName("王八蛋");
        int count = userMapper.insertGeneratedKeys(user);
        log.warn("count=={}", count);
        log.warn("user.getId()=={}", user.getId());
        // 插入一次
        User user2 = new User().setUserName("王八蛋2");
        userMapper.insertGeneratedKeys(user2);
        log.warn("user2.getId()=={}", user2.getId());


        List<User> userList = ListUtil.list(false);
        int COUNT = 3;
        for (int i = 0; i < COUNT; i++) {
            // 将id设置为自增了，不setId，防止报错
            userList.add(new User().setUserName("张三" + i));
        }

        // 批量插入，并返回自增主键
        userMapper.insertBatchGeneratedKeys(userList);
        log.warn("userList=={}", userList);
        sqlSession.commit();

        sqlSession.commit();
        sqlSession.close();
        log.info("插入/批量插入返回自增主键 end");
    }

    /**
     * $与#的区别,观察日志sql
     * #{}:是以预编译的形式，将参数设置到s.ql语句中，PreparedStatement;防止sgl注入
     * ${}:取出的值直接拼装在s.ql语句中;会有安全问题;
     * 大多情况下，我们去参数的值都应该去使用#{};
     * 原生jdbc不支持占位符的地方我们就可以使用${}进行取值比如分表;按照年份分表拆分
     * select *from $iyear}_salary where xxx3
     * select * from tbl_employee order by $if_hame} ${order}
     */
    public static void _$() {
        log.info("$与#的区别 start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper._$(1);
        log.warn("userList=={}", userList);

        sqlSession.close();
        log.info("$与#的区别 end");
    }


    /**
     * 判别器discriminator
     */
    public static void discriminator() {
        log.info("判别器discriminator start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper.discriminator();
        log.warn("userList=={}", userList);

        sqlSession.close();
        log.info("判别器discriminator end");
    }


    /**
     * bind
     */
    public static void bind() {
        log.info("bind start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper.bind("1");
        log.warn("userList=={}", userList);

        sqlSession.close();
        log.info("bind end");
    }


    /**
     * pageHelperHelloWorld
     */
    public static void pageHelperHelloWorld() {
        log.info("pageHelperHelloWorld start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        // 看日志sql
//        PageHelper.startPage(1, 2);
//        List<User> userList = userMapper.pageHelperHelloWorld();
//        log.warn("userList=={}", userList);

        // 看日志sql
        PageHelper.startPage(2, 2);
        List<User> userList2 = userMapper.pageHelperHelloWorld();
        log.warn("userList2=={}", userList2);

        sqlSession.close();
        log.info("pageHelperHelloWorld end");
    }


    /**
     * pageHelperUse 五种使用方式
     */
    public static void pageHelperUse() {
        log.info("pageHelperUse 五种使用方式 start");
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);


        // 看日志sql
        // 方式一
        RowBounds rowBounds = new RowBounds(3, 3);
        List<User> userList = sqlSession.selectList("top.lishuoboy.mybatis.mapper.UserMapper.pageHelper", "", rowBounds);
        log.warn("userList=={}", userList);

        // 方式一  PageRowBounds
        PageRowBounds pageRowBounds = new PageRowBounds(3, 3);
        pageRowBounds.setCount(false);
        pageRowBounds.setTotal(new Long(6));
        List<User> userList1_2 = sqlSession.selectList("top.lishuoboy.mybatis.mapper.UserMapper.pageHelper", "", pageRowBounds);
        log.warn("userList1_2=={}", userList1_2);

        // 第二种，Mapper接口方式的调用，推荐这种使用方式。
        PageHelper.startPage(2, 3);
        List<User> userList2 = userMapper.pageHelper();
        log.warn("userList2=={}", userList2);

        log.warn("userList2.size()=={}", userList2.size());
        for (int i = 0; i < userList2.size(); i++) {
            log.warn("userList2.get(i)=={}", userList2.get(i));
        }

        // 强转Page即可获取page信息
        Page userPage = (Page) userList2;
        log.warn("userPage.size()=={}", userPage.size());
        for (int i = 0; i < userPage.size(); i++) {
            log.warn("userPage.get(i)=={}", userPage.get(i));
        }
        log.error("userPage.getPageSize()=={}", userPage.getPageSize());
        log.error("userPage.getTotal()=={}", userPage.getTotal());

        // 第三种，Mapper接口方式的调用，推荐这种使用方式。
        PageHelper.offsetPage(3, 3);
        List<User> userList3 = userMapper.pageHelper();
        log.warn("userList3=={}", userList3);

        //第四种，参数方法调用。 存在以下 Mapper 接口方法，你不需要在 xml 处理后两个参数
        List<User> userList4 = userMapper.pageHelper(null, 2, 3);
        log.warn("userList4=={}", userList4);

        //第五种，参数对象
        List<User> userList5 = userMapper.pageHelper(new User().setPageNum(2).setPageSize(3));
        log.warn("userList5=={}", userList5);

        // 第四种 和 第五种 参数都有时，第四种失效
        List<User> userList4$5 = userMapper.pageHelper(new User().setPageNum(1).setPageSize(3), 2, 3);
        log.warn("userList4$5=={}", userList4$5);


        sqlSession.close();
        log.info("pageHelperUse 五种使用方式 end");
    }

}